﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Common;
using Transcend.Archive.CSScan.Store.Contract;
using Transcend.Utility;

namespace Transcend.Archive.CSScan.Stores
{
    /// <summary>
    /// 将图像存入Oracle数据库服务器
    /// </summary>
    public class OracleImageStore : ImageStore
    {
        /// <summary>
        /// 构造器
        /// </summary>
        public OracleImageStore() : this(0) { }
        /// <summary>
        /// 构造器
        /// </summary>
        /// <param name="storeId">存储Id</param>
        public OracleImageStore(decimal storeId)
            : base(storeId)
        {
            GetTableName = getStoreTableName;
            CreateImageTableProcName = "CREATEDSIMAGETBL";
        }

        private OracleStoreDescript DbDesc;
        private DbConnection conn;

        /// <summary>
        /// 创建存储图像数据表的存储过程名称，只有一个TableName参数
        /// </summary>
        public string CreateImageTableProcName { get; set; }
        /// <summary>
        /// 分析数据库存储的描述信息
        /// </summary>
        /// <param name="Descripts">描述</param>
        public override void Parse(string Descripts)
        {
            DbDesc = Newtonsoft.Json.JsonConvert.DeserializeObject<OracleStoreDescript>(Descripts);
            if (string.IsNullOrEmpty(DbDesc.ConnectionString))
            {
                throw new Exception(string.Format("存储({0})描述符中缺少ConnectionString或ConnectionString为空。", StoreId));
            }
            if (string.IsNullOrEmpty(DbDesc.Provider))
            {
                throw new Exception(string.Format("存储({0})描述符中缺少Provider或Provider为空。", StoreId));
            }

            if (DbDesc.InstanceOfTable <= 0)
            {
                throw new Exception(string.Format("存储({0})描述符中缺少InstanceOfTable参数或参数不正确，建议该参数为10000", StoreId));
            }
            var factory = DbProviderFactories.GetFactory(DbDesc.Provider);
            conn = factory.CreateConnection();
            conn.ConnectionString = DbDesc.ConnectionString;
            conn.Open();
        }

        /// <summary>
        /// 获取表名的方法委托
        /// </summary>
        public Func<decimal, decimal, int, string> GetTableName { get; set; }

        /// <summary>
        /// 默认获取表名的方法
        /// </summary>
        /// <param name="instanceId">实例Id</param>
        /// <param name="imageNo">图形Id</param>
        /// <param name="instanceOfTable">实例表</param>
        /// <returns></returns>
        string getStoreTableName(decimal instanceId, decimal imageNo, int instanceOfTable)
        {
            string tblName = "dsimage" + System.Convert.ToInt32(Math.Floor(instanceId / instanceOfTable));
            CreateImageTable(tblName);
            return tblName;
        }
        /// <summary>
        /// 保存图像到数据库系统
        /// </summary>
        public override void Save()
        {
            using (var trans = conn.BeginTransaction())
            {
                using (var cmd = conn.CreateCommand())
                {
                    try
                    {

                        foreach (var img in _images)
                        {
                            //string sql = "Select count(1) From "
                            string tableName = GetTableName(img.InstanceId, img.ImageNo, DbDesc.InstanceOfTable);
                            string sql = "Select count(1) from " + tableName + " where imgNo=:imgNo";
                            cmd.Parameters.Clear();
                            cmd.CommandText = sql;
                            var pm = cmd.CreateParameter();
                            pm.ParameterName = "imgNo";
                            pm.DbType = System.Data.DbType.Decimal;
                            pm.Direction = System.Data.ParameterDirection.Input;
                            pm.Value = img.ImageNo;
                            cmd.Parameters.Add(pm);
                            var cnt = System.Convert.ToInt32(cmd.ExecuteScalar());
                            if (cnt > 0)
                            {
                                Update(cmd, tableName, img.ImageNo, img.Content);
                            }
                            else
                            {
                                AddNew(cmd, tableName, img.ImageNo, img.Content);
                            }
                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// <summary>
        /// 更新图像
        /// </summary>
        /// <param name="imageNo">图形Id</param>
        /// <param name="content">新图形</param>
        protected void Update(DbCommand cmd, string tableName, decimal imageNo, byte[] content)
        {
            //更新图像
            cmd.Parameters.Clear();  //清除原来的参数
            string sql = "update " + tableName + " set content=:Content where imgno = :ImgNo";
            cmd.Parameters.Clear();
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;

            var cntPm = cmd.CreateParameter();
            cntPm.DbType = System.Data.DbType.Binary;
            cntPm.Direction = System.Data.ParameterDirection.Input;
            cntPm.ParameterName = "Content";
            cntPm.Value = content;
            cmd.Parameters.Add(cntPm);

            var imgnoPm = cmd.CreateParameter();
            imgnoPm.Direction = System.Data.ParameterDirection.Input;
            imgnoPm.DbType = System.Data.DbType.Decimal;
            imgnoPm.Value = imageNo;
            imgnoPm.ParameterName = "ImgNo";
            cmd.Parameters.Add(imgnoPm);


            cmd.ExecuteNonQuery();
            //重新文件长度
            sql = "update dspage set imgsize={0} where imgno={1}";
            Db.InitCommand(cmd, sql, content.Length, imageNo);
            cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 新建图像
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="tableName"></param>
        /// <param name="imageNo"></param>
        /// <param name="content"></param>
        protected void AddNew(DbCommand cmd, string tableName, decimal imageNo, byte[] content)
        {

            string sql = "insert into " + tableName + "(imgno,content) values ( :ImgNo, :Content)";

            cmd.Parameters.Clear();
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;

            var imgnoPm = cmd.CreateParameter();
            imgnoPm.Direction = System.Data.ParameterDirection.Input;
            imgnoPm.DbType = System.Data.DbType.Decimal;
            imgnoPm.Value = imageNo;
            imgnoPm.ParameterName = "ImgNo";
            cmd.Parameters.Add(imgnoPm);

            var cntPm = cmd.CreateParameter();
            cntPm.DbType = System.Data.DbType.Binary;
            cntPm.Direction = System.Data.ParameterDirection.Input;
            cntPm.ParameterName = "Content";
            cntPm.Value = content;

            cmd.Parameters.Add(cntPm);

            cmd.ExecuteNonQuery();

            //重新文件长度
            sql = "update dspage set imgsize={0} where imgno={1}";
            Db.InitCommand(cmd, sql, content.Length, imageNo);
            cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 创建图像存储表
        /// </summary>
        /// <param name="tblName"></param>
        /// <returns></returns>
        protected virtual void CreateImageTable(string tblName)
        {

            Transcend.Utility.Db.ExecProcedure(conn, CreateImageTableProcName, tblName);

        }
        /// <summary>
        /// 从数据库获取图像
        /// </summary>
        /// <param name="imageNo"></param>
        /// <returns></returns>
        //public byte[] GetImage(ScanImage img)
        //{
        //    string tableName = GetTableName(img.InstanceId, img.ImageNo, DbDesc.InstanceOfTable);

        //    string sql = "Select Content From " + tableName + " where imgno = :ImgNo";
        //    using (var cmd = conn.CreateCommand())
        //    {
        //        cmd.Parameters.Clear();
        //        cmd.CommandText = sql;
        //        cmd.CommandType = System.Data.CommandType.Text;
        //        //InitialLOBFetchSize,InitialLONGFetchSize
        //        var cmdType = cmd.GetType();
        //        var pi = cmdType.GetProperty("InitialLOBFetchSize");
        //        if (pi != null)
        //        {
        //            pi.SetValue(cmd, -1, null);
        //        }
        //        pi = cmdType.GetProperty("InitialLONGFetchSize");
        //        if (pi != null)
        //        {
        //            pi.SetValue(cmd, -1, null);
        //        }

        //        var imgnoPm = cmd.CreateParameter();
        //        imgnoPm.Direction = System.Data.ParameterDirection.Input;
        //        imgnoPm.DbType = System.Data.DbType.Decimal;
        //        imgnoPm.Value = img.ImageNo;
        //        imgnoPm.ParameterName = "ImgNo";
        //        cmd.Parameters.Add(imgnoPm);
        //        byte[] buffer = null;
        //        using (var reader = cmd.ExecuteReader())
        //        {
        //            if (reader.Read())
        //            {
        //                var bytes = reader.GetValue(0);
        //                if (bytes is byte[]) buffer = (byte[])bytes;
        //            }
        //        }
        //        return buffer;
        //    }
        //}
        /// <summary>
        /// 获取图像
        /// </summary>
        /// <param name="instid">实例Id</param>
        /// <param name="imgNo">图像Id</param>
        /// <returns></returns>
        public override byte[] GetImage(decimal instid, decimal imgNo)
        {
            string tableName = GetTableName(instid, imgNo, DbDesc.InstanceOfTable);

            string sql = "Select Content From " + tableName + " where imgno = :ImgNo";
            using (var cmd = conn.CreateCommand())
            {
                cmd.Parameters.Clear();
                cmd.CommandText = sql;
                cmd.CommandType = System.Data.CommandType.Text;
                //InitialLOBFetchSize,InitialLONGFetchSize
                var cmdType = cmd.GetType();
                var pi = cmdType.GetProperty("InitialLOBFetchSize");
                if (pi != null)
                {
                    pi.SetValue(cmd, -1, null);
                }
                pi = cmdType.GetProperty("InitialLONGFetchSize");
                if (pi != null)
                {
                    pi.SetValue(cmd, -1, null);
                }

                var imgnoPm = cmd.CreateParameter();
                imgnoPm.Direction = System.Data.ParameterDirection.Input;
                imgnoPm.DbType = System.Data.DbType.Decimal;
                imgnoPm.Value = imgNo;
                imgnoPm.ParameterName = "ImgNo";
                cmd.Parameters.Add(imgnoPm);
                byte[] buffer = null;
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var bytes = reader.GetValue(0);
                        if (bytes is byte[]) buffer = (byte[])bytes;
                    }
                }
                return buffer;
            }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public override void Dispose()
        {
            if (conn != null)
            {
                if (conn.State == System.Data.ConnectionState.Open) conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 删除图像
        /// </summary>
        /// <param name="instanceId">实例Id</param>
        /// <param name="imageNo">图形Id</param>
        /// <returns>图形Id</returns>
        public override int Delete(decimal instanceId, decimal imageNo)
        {
            string tableName = GetTableName(instanceId, imageNo, DbDesc.InstanceOfTable);
            string sql = "delete From " + tableName + " where imgno = :ImgNo";
            DbParameter p = null;
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = sql;
                p = cmd.CreateParameter();
                p.Value = imageNo;
                cmd.Parameters.Add(p);
                return cmd.ExecuteNonQuery();
            }
        }


        /// <summary>
        /// 文件更名
        /// </summary>
        /// <param name="img">图形描述</param>
        public override void ChangeFileName(ScanImage img)
        {
            ScanImageDetail page = null;
            if ((img is ScanImageDetail))
            {
                page = (ScanImageDetail)img;
            }
            else
            {
                if (img.Tag != null && img.Tag is ScanImageDetail)
                {
                    page = img.Tag as ScanImageDetail;
                }
            }
            if (page == null)
            {
                throw new Exception("文件更名需要图像的详细信息。");
            }
            string fileName = GetFileName(page);
            fileName += System.IO.Path.GetExtension(img.FileName);
            string sql = "Update dspage p set p.RAWFILENAME={0}  where p.imgno={1}";
            using (var cmd = conn.CreateCommand())
            {
                Db.InitCommand(cmd, sql, fileName, page.ImageNo);
                cmd.ExecuteNonQuery();
            }
        }


        public override void ChangeFileName(decimal imageNo, string newName)
        {
            using (var cmd = conn.CreateCommand())
            {
                using (var trans = conn.BeginTransaction())
                {
                    string sql = "select instid from dsitem i,dspage p where p.itemid=i.itemid and p.imgno={0}";
                    var instId = Db.ExecuteScaler<decimal>(conn, sql, imageNo);
                    if (instId == 0) throw new Exception("无效的图形编号，找不到相应存储实例。");

                    sql = "select RAWFILENAME from dspage where imgno={0}";
                    string oldName = Db.ExecuteScaler<string>(conn, sql, imageNo);
                    if (string.IsNullOrEmpty(oldName)) throw new Exception("无效的图形，找不到找不到图形对应的文件名称。");

                    try
                    {
                        sql = "Update dspage p set p.RAWFILENAME={0} where p.IMGNO={1}";
                        Db.InitCommand(cmd, sql, newName, imageNo);
                        cmd.Transaction = trans;
                        cmd.ExecuteNonQuery();   //更新文件名                        
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }

                }
            }
        }
    }
}
